#!/usr/bin/python -O


from bottle import Bottle, request, response, template, run
from datetime import datetime, timedelta
import pyodbc

connstr = "Driver=Vertica;Server=c0043156.itcs.hp.com;Port=5433;Database=squeen;UID=squeen;PWD=redhat06"

conn = pyodbc.connect(connstr)

app = Bottle()

def get_social_ids(uid):
    cursor = conn.cursor()
    querystr = """SELECT account_id, social_id, message_source
            FROM sq.user_config
            WHERE uid = ?
                   """

    if __debug__:
        print querystr

    users = [ {'account_id': row.account_id, 
               'social_id' : row.social_id,
               'message_source' : row.message_source}
               for row in cursor.execute(querystr, [uid])]

    return {"uid" : uid,
            "_total" : len(users),
            "users" : users}

def get_profile_bysocial(social_id):
    cursor = conn.cursor()
    querystr = """SELECT social_id, social_name, company_name, company_id,
                         title, location, picture
            FROM sq.social_profile
            WHERE social_id = ?
            ORDER BY version
            LIMIT 1
                   """

    if __debug__:
        print querystr

    cursor.execute(querystr, [social_id])
    row = cursor.fetchone()

    if row:
        return {"_total" : 1,
                "profile" : {"social_id"   : row.social_id.strip(), 
                             "social_name" : row.social_name.strip(),
                             "company_id"  : row.company_id,
                             "company_name" : row.company_name.strip(),
                             "title" : row.title.strip(),
                             "location" : row.location.strip(),
                             "picture" : row.picture.strip(),
                             }
               }
    else:
        return {"_total" : 0,
                "profile" : {}
               }


def get_messages_by_social(social_ids):
    idlist = [ "'{0}'".format(id) for id in social_ids.split(",") ]
    if __debug__:
        print idlist

    cursor = conn.cursor()
    querystr = """SELECT social_id, message_id, message_ts,
               message_text, message_source, account_id
        FROM sq.social_message
        WHERE social_id IN ({0})
               """.format(",".join(idlist))
    queryconds = []

    from_ts = request.query.get("from")
    if from_ts:
        try:
            from_date = datetime.strptime(from_ts, "%Y-%m-%d")
        except ValueError:
            return { "_total" : 0,
                     "error" : "Incorrect date format for 'from'," \
                               "accepted format is YYYY-MM-DD"}

        querystr += " AND message_ts >= ?"
        queryconds.append(from_date)

    to_ts   = request.query.get("to")
    if to_ts:
        try:
            to_date = datetime.strptime(to_ts, "%Y-%m-%d") \
                     + timedelta(hours=23, minutes=59)
        except ValueError:
            return { "_total" : 0,
                     "error" : "Incorrect date format for 'to', " \
                               "accepted format is YYYY-MM-DD"}

        querystr += " AND message_ts <= ?"
        queryconds.append(to_date)

    querystr += " ORDER BY message_ts desc LIMIT 3000"
    if __debug__:
        print querystr
        print queryconds

    messages = [{"social_id" : row.social_id,
                 "account_id": row.account_id,
                 "message_id": row.message_id,
                 "message_ts": row.message_ts.strftime("%Y-%m-%d %H:%M:%S"),
                 "message_text": row.message_text,
                 "message_source": row.message_source}
                   for row in cursor.execute(querystr, queryconds) ] 

    return {"_total" :  len(messages),
            "from"   : from_ts,
            "to"     : to_ts,
            "messages" : messages }


def get_messages_by_account(account_ids):
    idlist = [ "'{0}'".format(id) for id in account_ids.split(",") ]
    if __debug__:
        print idlist

    cursor = conn.cursor()
    querystr = """SELECT social_id, message_id, message_ts,
               message_text, message_source, account_id
        FROM sq.social_message
        WHERE account_id IN ({0})
               """.format(",".join(idlist))
    queryconds = []

    from_ts = request.query.get("from")
    if from_ts:
        try:
            from_date = datetime.strptime(from_ts, "%Y-%m-%d")
        except ValueError:
            return { "_total" : 0,
                     "error" : "Incorrect date format for 'from'," \
                               "accepted format is YYYY-MM-DD"}

        querystr += " AND message_ts >= ?"
        queryconds.append(from_date)

    to_ts   = request.query.get("to")
    if to_ts:
        try:
            to_date = datetime.strptime(to_ts, "%Y-%m-%d") \
                     + timedelta(hours=23, minutes=59)
        except ValueError:
            return { "_total" : 0,
                     "error" : "Incorrect date format for 'to', " \
                               "accepted format is YYYY-MM-DD"}

        querystr += " AND message_ts <= ?"
        queryconds.append(to_date)

    querystr += " ORDER BY message_ts desc LIMIT 3000"
    if __debug__:
        print querystr
        print queryconds

    messages = [{"social_id" : row.social_id,
                 "account_id": row.account_id,
                 "message_id": row.message_id,
                 "message_ts": row.message_ts.strftime("%Y-%m-%d %H:%M:%S"),
                 "message_text": row.message_text,
                 "message_source": row.message_source}
                   for row in cursor.execute(querystr, queryconds) ] 

    return {"_total" :  len(messages),
            "from"   : from_ts,
            "to"     : to_ts,
            "messages" : messages }


@app.get("/sq")
def hello():
    return "Welcome to Social Queen"


@app.get("/sq/social_id")
def social_ids():
    uid = request.query.get("uid")
    if uid is None:
        return {"error": "uid not provided", "_total": 0}
    else:
        return get_social_ids(uid)


@app.get("/sq/profile/<social_id:path>")
def profile(social_id=None):
    if social_id is None:
        return {"error": "social id not provided", "_total": 0}
    else:
        return get_profile_bysocial(social_id)


@app.get("/sq/message_bysocial/<social_ids:path>")
def message_bysocial(social_ids=None):
    if social_ids is None:
        return { "_total" : 0,
                 "error" : "No such social id or social id is empty"}
    else:
        return get_messages_by_social(social_ids)


@app.get("/sq/message_byaccount/<account_ids:path>")
def message_byaccount(account_ids=None):
    if account_ids is None:
        return { "_total" : 0,
                 "error" : "No such account id or social id is empty"}
    else:
        return get_messages_by_account(account_ids)


import socket
import sys
port = 9000
if len(sys.argv) > 1:
    port = int(sys.argv[1])

run(app, host=socket.getfqdn(), port=port, debug=True)

